Configuring: .NET Script

Select a dataset to which you want to add new fields. Write a script that will give values to these fields. This operation will modify the existing dataset by adding the new fields.

For this operation to execute it is important to verify the script. This operation will only give the desired outputs, once all compiler errors given by the selected .NET language when verifying the script, are corrected.

Worked example: .NET script

Steps to configure

.NET script: Add new fields, and write the script to give values to these fields.
- First add a new field name in the Outputs tab text box, select the data type and add it to the required outputs list using the green [+].
- Add as many new fields as required in this manner.
- Declare all variables in the Variables tab - the values of which need to be persisted through all executions of the script, which occurs for each row in the dataset.
- Write the script to create values for the dataset rows for the new fields.
Execution: The existing dataset will be modified to include the newly added fields.

Step 1: .NET script

Add new fields, and write the script to give values to these fields.

A default script is shown when the operation is started up. This gives an example of how to use the FirstExecute property.
Write the script in either of the available .NET languages.
Declare the new fields, as well as other variables in the script that must be persisted between row executes.
The script must be verified before [Finish] can be clicked.

Toolbar buttons:

New – Clear the text box where the example script is entered.
Open – Opens any .txt file or other text file type and sets the script to its contents.
Save – Saves the current script as a .txt file.
Cut/Copy/Paste – Manipulates the text in the script text area.
Verify script – This has to be clicked to verify any script before the [Finish] button can be clicked and operation performed. F7 pressed on the keyboard will also verify the script.
Timestamp - define the time type to use in the script. Default time type = UTC. Click the button to toggle to [Local time] or click again to revert back to UTC time.

Language menu:

The chosen programming language can be set to either C# (C Sharp) or VB.NET (Visual Basic .NET). Note that this cannot be changed once one or more script variables have been added (under the Variables tab). Delete these variables before switching the language.

Timestamps:

For input fields:

- All input fields of DateTime type will have its time type set to the selected setting: either Local Time or UTC time.
- The selected time type setting does not change the actual value of the timestamp field, merely the time type that the timestamp is being interpreted as when being used in the script.

For output fields:

- Output fields of DateTime type that have an unspecified time type, will be interpreted as the selected time type setting, but always displayed to the user as Local Time.
- Be aware that defining the time type setting by toggling between the UTC and Local Time buttons will change the timestamp value displayed when the output field’s time type was unspecified.

Script text box:

This is where you enter your script. This script is executed for every row of the modified dataset.

The current fields in the dataset (listed under the Inputs tab) will have their appropriate values for that row. Variables can be declared in the script itself, but their values will be reinitialized when consequent rows are executed. The Variables tab can be used to declare variables that will retain their values through all row executions. The initial values or instantiation of these variables should always be done when a script variable “FirstExecute” is set to a Boolean ‘true’. A default script is shown when the operation is started up. This gives an example of how to use the FirstExecute property.

When you have finishing coding the script, click [Verify Script] (or F7) to check the script. Any errors will be shown in the errors list below the script text box. See the list of possible exceptions here.

Errors list:

All errors that are found in the script after the script is verified are shown in a list view below the script text box. Double clicking an error will take you to the relevant error line. For more information on the error that occurred, check Microsoft (TM) .NET documentation on the particular error code shown under the error description.

Limitations:

Read more on limitations of the .NET script.

Outputs tab:

On the right-hand side of the page, all new output fields that are added to the dataset are listed. They can be either a double, integer, string or date type. Any number of output fields can be created. Setting a value in the script for a particular field will add that value into the dataset for the current row's execute.

The output fields are all declared in the .NET script as nullable types. These values can be read or written as their respective types or a value of “null’. If a specific .NET method that you wish to call needs one of these script fields as a parameter, the value of the output field can be accessed with the “.Value” property (e.g. Math.Round(InputField1.Value) ).

Inputs tab:

All current fields in a dataset of types Double, Integer, String or DateTime can be accessed as input fields in this script. These current fields, listed on the Inputs tab, will have their appropriate values for that row.

The input fields are all declared in the .NET script as nullable types. These values can be read as either their respective types or a value of “null’. If a specific .NET method that you wish to call needs one of these script fields as a parameter, the value of the output field can be accessed with the “.Value” property (e.g. Math.Round(InputField1.Value) ).

Data Quality:

When defining the data quality within a data preparation operation, data quality is not explicitly set to GOOD or BAD. Rather the field value is set to bad quality by setting it to the value 'null' in C# or the value 'Nothing' in VB. In the script, these fields are nullable types.

Variables tab:

Two text boxes are provided here to enter a variable name (top text box) and a variable type (bottom text box). The variable can be the built in Field<T> type or any type that is supported by the programming language you choose and the included namespaces (See “Namespaces” below). When a type is entered that is not supported, the field will not be added and errors when trying to add the new field type will be shown in the Errors list.

Note that the variables added here need to be initialized in a “if FirstExecute” type clause, so that they are only initialized for the execute of the first row. The values of these fields will persist through all executions of this script (which is done for every dataset row). Variables can be declared inline in the script itself, but will not retain its state across different executes.

Namespaces:

The script has all features provided by the chosen .NET language. Click here to see the included namespaces.

Step 2: Execute

The dataset will be modified to include the added fields and the values as specified in the script.